package com.vanke.agent.sync;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.HashSet;
import java.util.Set;

import org.apache.log4j.Logger;

import com.vanke.agent.ADataSource;
import com.vanke.agent.C;
import com.vanke.agent.bean.FollowRecord;
import com.vanke.agent.common.CstUtil;
import com.vanke.agent.common.TradeUtil;
import com.vanke.agent.common.log.LoggerUtil;

public class GetInSync extends AgentSync {
	Logger log = LoggerUtil.getLoggerByName("getin_sync");
	protected Set<String> aready = new HashSet<String>();
	
	@Override
	public void sync() {
		long now = System.currentTimeMillis();
		int added = 0, record = 0;
		// int date_added = 0, date_updated = 0, date_record = 0;
		int date_added = 0, date_record = 0;
		// Date startDate = getinCfg.beginTime;
		// Calendar startCal = Calendar.getInstance();
		// startCal.setTime(startDate);
		// Calendar nowCal = Calendar.getInstance();
		Connection con = null;
		Connection wcon = null;
//		String startTime = "";
//		String endTime = "";
		log.info("-----------sync getin start------------");
		// do {
			try {
				con = ADataSource.getConnection(ADataSource.SCHEMA_MY);
				wcon = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);
				// startTime = dateFormat.format(startCal.getTime());
				// startCal.add(Calendar.DAY_OF_MONTH, 1);
				// endTime = dateFormat.format(startCal.getTime());
				// startCal.add(Calendar.DAY_OF_MONTH, -1);// 等真正执行完成之后再+1
				String fields = C.FIELDS_GETIN;
				String table = "s_Getin";
				date_added = 0;
				// date_updated = 0;
				date_record = 0;
				String querySql = "SELECT " + fields + " FROM s_Getin where GetDate >='2014-09-01' order by GetDate asc";
				// String querySql = "SELECT " + fields + "  FROM s_Getin";
				// querySql += " where GetDate >= ? AND GetDate < ? order by GetDate asc";
//				log.info("execute: " + querySql + "[" + startTime + "~"
//						+ endTime + "]");
				log.info("execute: " + querySql);
				//				sleep(10000);
				// 缓存现有的回款GUID
				loadGUID(wcon);

				// 新增
				String insertSql = CstUtil.formatInsertSql(table, fields);
				PreparedStatement wpstmt = wcon.prepareStatement(insertSql);
				int columns = fields.split(",").length;

				// 更新
//				String updateSql = CstUtil.formatUpdateSql(table, fields);
//				updateSql += " where GetinGUID = ? ";
//				PreparedStatement updatePstmt = wcon
//						.prepareStatement(updateSql);

				PreparedStatement pstmt = con.prepareStatement(querySql,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
//				pstmt.setString(1, startTime);
//				pstmt.setString(2, endTime);

				ResultSet rs = pstmt.executeQuery();

				while (rs.next()) {
					date_record++;
					getinCfg.beginTime = new java.sql.Date(rs.getTimestamp(
							"GetDate").getTime());
					String getinGUID = rs.getString("GetinGUID");
					boolean exists = aready.contains(getinGUID);//exists(wcon, getinGUID);
					try {
						if (exists) {
							log.info("getin[" + getinGUID
									+ "] aready exists in datacenter");
//							for (int i = 1; i <= columns; i++) {
//								updatePstmt.setString(i, rs.getString(i));
//							}
//							updatePstmt.setString(columns + 1, getinGUID);
//							updatePstmt.executeUpdate();
//							date_updated++;
							continue;
						} // end if (exists)
						log.info("save getin[" + getinGUID + "]");
						for (int i = 1; i <= columns; i++) {
							wpstmt.setString(i, rs.getString(i));
						}
						wpstmt.executeUpdate();
						date_added++;
					} catch (Exception e) {
						log.error("save or update getin[" + getinGUID
								+ "] error!", e);
					}
					syncTrade2Cst(con, wcon, rs.getString("SaleGUID"),
							rs.getTimestamp("GetDate"), getinGUID);
				}
					// 保存配置，最后一次的时间
//				Cfg.set(getinCfg.cfgKey,
//						dateFormat.format(startCal.getTime()));
//				Cfg.save();
//				startCal.add(Calendar.DAY_OF_MONTH, 1);
				// 关闭
				ADataSource.close(rs);
				ADataSource.close(pstmt);
				ADataSource.close(wpstmt);

//				nowCal.setTime(new Date(System.currentTimeMillis()));
//				nowCal.set(Calendar.HOUR_OF_DAY, 0);
//				nowCal.set(Calendar.MINUTE, 0);
//				nowCal.set(Calendar.SECOND, 0);
//				nowCal.set(Calendar.MILLISECOND, 0);
			} catch (Exception e) {
				e.printStackTrace();
				log.error("sync getin error:" + e.getMessage(), e);
			} finally {
				long spendTime = System.currentTimeMillis() - now;
				log.info(date_added
						+ " getin added, by "
						+ date_record + " records. spend " + spendTime + " ms");
				added += date_added;
				// updated += date_updated;
				record += date_record;
				ADataSource.close(con);
				ADataSource.close(wcon);
			}
		//} while (startCal.before(nowCal));

		long spendTime = System.currentTimeMillis() - now;
		log.info(added + " getin added, by "
				+ record + " records. spend " + spendTime + " ms");
		log.info("-----------sync getin end------------");
	}// ~sync

	void syncTrade2Cst(Connection myCon, Connection bizCon, String tradeGUID,
			Timestamp time, String getinGUID) {
		// Connection myCon = ADataSource.getConnection(ADataSource.SCHEMA_MY);
		PreparedStatement myPstmt = null;
		ResultSet rs = null;

		// Connection bizCon =
		// ADataSource.getConnection(ADataSource.SCHEMA_BIZ);
		PreparedStatement bizPstmt = null;

		String fields = C.FIELDS_TRADE2CST;

		try {
			// 获取签约金额
			String sql = "select htTotal,projGUID from s_Contract where status= ? and TradeGUID = ? ";
			// log.info(sql);
			myPstmt = myCon.prepareStatement(sql);
			myPstmt.setString(1, "激活");
			myPstmt.setString(2, tradeGUID);
			rs = myPstmt.executeQuery();
			// 如果没查到，记录日志，结束
			if (!rs.next()) {
				log.info("no Contract found for TradeGUID: " + tradeGUID);
				return;
			}
			double httotal = rs.getDouble("htTotal");// 签约金额
			String projGUID = rs.getString("projGUID");
			// log.info(httotal+","+projGUID);
			// 获取总回款金额
			sql = "select sum(Amount) from s_Getin where SaleGUID = ? ";
			bizPstmt = bizCon.prepareStatement(sql);
			bizPstmt.setString(1, tradeGUID);
			rs = bizPstmt.executeQuery();
			rs.next();
			double amount = rs.getDouble(1);// 回款总金额
			if (amount >= httotal) {
				// 查找关联表
				sql = "select " + fields
						+ " from s_Trade2Cst  where TradeGUID=?";
				myPstmt = myCon.prepareStatement(sql);
				myPstmt.setString(1, tradeGUID);
				rs = myPstmt.executeQuery();

				// 如果没查到，记录日志，结束
				if (!rs.next()) {
					log.info("no Trade2Cst found for TradeGUID: " + tradeGUID);
					return;
				}
				// 准备写
				// String insertSql = CstUtil.formatInsertSql("s_Trade2Cst",
				// fields);
				// bizPstmt = bizCon.prepareStatement(insertSql);
				// int columns = fields.split(",").length;
				do {
					/*
					 * try { // 保存关联表 for (int i = 1; i <= columns; i++) {
					 * bizPstmt.setString(i, rs.getString(i)); }
					 * bizPstmt.executeUpdate(); } catch (Exception e) { //
					 * log.error("getin save trade2cst for " + tradeGUID // + "
					 * error:" + e); }
					 */
					String projCode = getProjectCode(myCon, projGUID);

					// 根据明源GUID查找数据中心客户GUID
					String oriCstGUID = rs.getString("CstGUID");
					long uid = CstUtil.getCstGUIDByOriGuid(bizCon, oriCstGUID);
					if (uid == 0) {
						// 查不到，则到明源查义主档记录的身份证或手机，再反查数据中心
						log.info("no CstGUID found for:" + oriCstGUID
								+ ", retrieve again");
						uid = CstUtil.retrieveMemship(oriCstGUID);
					}
					// 判断该用户回款跟进是否存在
					String frTable = CstUtil.retrieveCstReceiveTable(uid);
					sql = "select PkIndex from "
							+ frTable
							+ " where TradeGUID=? and ProjGUID=? and CstGUID=? and followType=7";
					bizPstmt = bizCon.prepareStatement(sql);
					bizPstmt.setString(1, tradeGUID);
					bizPstmt.setString(2, projGUID);
					bizPstmt.setLong(3, uid);
					rs = bizPstmt.executeQuery();
					// 如果查到，记录日志，结束
					if (rs.next()) {
						log.info("cst_FollowRecord found for TradeGUID: "
								+ tradeGUID + ",ProjGUID:" + projGUID
								+ ",CstGUID:" + uid);
						return;
					}
					String comment = "回款";
					FollowRecord fr = new FollowRecord();
					fr.setCstGUID(uid);
					fr.setOriCstGUID(oriCstGUID);
					fr.setProjGUID(projGUID);
					fr.setProjCode(projCode);
					fr.setReceiveType("回款");
					fr.setFollowType( C.FOLLOW_GETIN);
					fr.setCstType(4);
					fr.setReceiveDate(time);
					fr.setTotal(httotal);
					fr.setTradeGUID(tradeGUID);
					fr.setComment(comment);
					fr.setRoomGUID("");
					fr.setRoom("");
					fr.setHuxing("");
					fr.setRefGUID(getinGUID);
					TradeUtil.saveFollowRecord(bizCon, fr, log);
//					TradeUtil.saveFollowRecord(bizCon, uid, oriCstGUID,
//							projGUID, projCode, "回款", 7, 4, time, httotal,
//							tradeGUID, comment, "", "", "", log);
					if (uid == 0) {
						// 最终未找到
						log.warn("SYNC_WARN[05]关联客户在数据中心未找到[" + oriCstGUID
								+ "]");
						continue;
					}
					TradeUtil.sendNotice(C.NOTICE_GETIN, 	// 消息类型
							uid,				// 客户GUID 
							projCode, 			// 项目编号
							time, 				// 时间
							httotal, 			// 金额
							C.FOLLOW_GETIN,		// 跟进类型
							comment, 			// 备注说明
							C.TRADE_STATUS_NONE,//
							null, null, null);
				} while (rs.next());// end while (rs.next())
			} else {
				log.info("getin-invalid,sum(amount):" + amount + ",htTotal:"
						+ httotal);
			}

		} catch (Exception e) {
			e.printStackTrace();
			log.error("getin handle Trade2Cst error!", e);
		} finally {
			ADataSource.close(rs, myPstmt, null);
			ADataSource.close(null, bizPstmt, null);
		}
	} // ~syncTrade2Cst

//	boolean exists(Connection con, String getinGUID) {
//		String sql = "select GetinGUID from s_Getin where GetinGUID=?";
//		PreparedStatement pstmt = null;
//		ResultSet rs = null;
//		try {
//			pstmt = con.prepareStatement(sql);
//			pstmt.setString(1, getinGUID);
//			rs = pstmt.executeQuery();
//			return rs.next();
//		} catch (Exception e) {
//			e.printStackTrace();
//			log.error("exists error!", e);
//		} finally {
//			ADataSource.close(rs, pstmt, null);
//		}
//		return false;
//	}
	
	/* 缓存已有的GUID */
	protected void loadGUID(Connection con) throws SQLException {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("select GetinGUID from s_Getin");
			while (rs.next()) {
				aready.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			ADataSource.close(rs, stmt, null);
		}
	} //~
}
